﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.Linq;

namespace RemoveSP_ExecuteSql
{
    class Program
    {
        static string sourcePath;
        static string destinationPath;

        static void Main(string[] args)
        {
            if (args.Length != 2 || !validateArgs(args))
            {
                showHelp();
                return;
            }
            processFile();
        }

        private static void processFile()
        {
            using (FileStream sourceFile = File.Open(sourcePath, FileMode.Open, FileAccess.Read, FileShare.None))
            {
                using (XmlReader reader = XmlReader.Create(sourceFile, new XmlReaderSettings()))
                {
                    reader.MoveToContent();
                    XDocument doc = XDocument.Load(reader);

                    XName columnNodeName = XName.Get("Column", "http://tempuri.org/TracePersistence.xsd");
                    XName nameNodeName = XName.Get("name");
                    var elementsToProcess = doc.Descendants().Where(element =>
                        element.Name == columnNodeName && 
                        element.Attribute(nameNodeName) != null &&
                        element.Attribute(nameNodeName).Value == "TextData" &&
                        element.Value.IndexOf("sp_executesql") > -1);

                    foreach (var element in elementsToProcess)
                        element.Value = removeExecuteSql(element.Value);

                    doc.Save(destinationPath);
                }
            }
        }

        private static string removeExecuteSql(string origSql)
        {
            // from http://archive.msdn.microsoft.com/spExecuteSqlparser

            string tmp = origSql.Replace("''", "~~");       // Temporary replacement to simplify matching isolated single quotes
            string baseSql;
            string paramTypes;
            string paramData = "";
            int i0 = tmp.IndexOf("'") + 1;
            int i1 = tmp.IndexOf("'", i0);
            if (i1 > 0)
            {
                baseSql = tmp.Substring(i0, i1 - i0);       // Main SQL statement is first parameter in single quotes
                i0 = tmp.IndexOf("'", i1 + 1);
                i1 = tmp.IndexOf("'", i0 + 1);
                if (i0 > 0 && i1 > 0)
                {
                    paramTypes = tmp.Substring(i0 + 1, i1 - i0 - 1);     // This is not required, but retained for reference
                    paramData = tmp.Substring(i1 + 1);
                }
            }
            else
                throw new Exception("Cannot identify SQL statement in first parameter");
            // Undo initial temp replacement, and convert to single instance of single quote
            baseSql = baseSql.Replace("~~", "'");           
            paramData = paramData.Replace("~~", "'");           
            if (!String.IsNullOrEmpty(paramData))           // Check if there are any parameters to replace
            {
                string[] paramList = paramData.Split(",".ToCharArray());
                foreach (string paramValue in paramList)
                {
                    int iEq = paramValue.IndexOf("=");
                    if (iEq < 0)
                        continue;
                    string pName = paramValue.Substring(0, iEq).Trim();
                    string pVal = paramValue.Substring(iEq + 1).Trim();
                    baseSql = baseSql.Replace(pName, pVal);
                }
            }
            return baseSql;
        }

        private static bool validateArgs(string[] args)
        {
            sourcePath = args[0];
            if (!File.Exists(sourcePath))
                return false;

            destinationPath = args[1];
            try
            {
                if (!Path.IsPathRooted(destinationPath))
                    destinationPath = Path.GetFullPath(destinationPath);
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        private static void showHelp()
        {
            Console.WriteLine("sp_executesql Remover");
            Console.WriteLine("");
            Console.WriteLine("This tool replaces sp_executesql calls in a XML trace file saved with SQL Server Profiler with their content so that SQL Tuning Advisor can process them.");
            Console.WriteLine("");
            Console.WriteLine("Sintax: RemoveSP_ExecuteSql source destination");
            Console.WriteLine("");
            Console.WriteLine("source\t\tThe source XML trace file.");
            Console.WriteLine("destination\tThe file the fixed trace will be written to.");
        }
    }
}
